package com.ncmem.up7.sql;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.ncmem.utils.ConfigReader;
import com.ncmem.up7.database.DBConfig;
import com.ncmem.up7.database.DbHelper;
import com.ncmem.up7.XDebug;
import org.apache.commons.lang.StringUtils;

import com.jayway.jsonpath.JsonPath;
import com.jayway.jsonpath.ReadContext;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class SqlExec {
    ReadContext m_table;
    SqlValSetter m_svs;
    SqlCmdReader m_scr;

    public SqlExec() {
        this.m_svs = new SqlValSetter();
        this.m_scr = new SqlCmdReader();
    }

    public ReadContext table(String tableName)
    {
        ConfigReader cr = new ConfigReader();
        JSONObject o = cr.module(String.format("database.%s", tableName));
        return JsonPath.parse(o);
    }

    public void exec(String table,String sql,String fields,String where,JSONObject o)
    {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);

        DbHelper db = new DbHelper();
        PreparedStatement cmd= db.GetCommand(sql);
        this.m_svs.set(cmd, field_sel, o);
        db.ExecuteNonQuery(cmd);
    }

    public void exec(String sql)
    {
        DbHelper db = new DbHelper();
        db.ExecuteNonQuery(sql);
    }

    public long execLong(String sql)
    {
        DbHelper db = new DbHelper();
        PreparedStatement cmd= db.GetCommand(sql);
        return db.ExecuteLong(cmd);
    }

    public JSONArray exec(String table,String sql,String fields,String newNames)
    {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);
        String[] names = fields.split(",");
        if( !StringUtils.isBlank(newNames) ) names = newNames.split(",");

        JSONArray arr = new JSONArray();

        DbHelper db = new DbHelper();
        PreparedStatement cmd= db.GetCommand(sql);
        ResultSet r = db.ExecuteDataSet(cmd);
        try {
            while(r.next())
            {
                JSONObject item = new JSONObject();
                for(int i = 0 ; i < field_sel.size();++i)
                {
                    JSONObject field = (JSONObject) field_sel.get(i);
                    item.put(names[i], this.m_scr.read(r, field,i));
                }
                arr.add(item);

            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return arr;
    }

    public void exec_batch(String table,String sql,String fields,String where,JSONArray values)
    {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);
        JSONArray field_where = this.selFields(where);


        DbHelper db = new DbHelper();

        Connection con = db.GetCon();
        try {
            PreparedStatement cmd = con.prepareStatement(sql);
            for(int i = 0 ; i < values.size(); ++i)
            {
                JSONObject val = (JSONObject)values.get(i);
                this.m_svs.set(cmd, field_sel, val);
                this.m_svs.set(cmd, field_where, val);
                cmd.execute();
            }
            con.close();
            cmd.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    //从字段表中选择字段
    public JSONArray selFields(String fields)
    {
        if( StringUtils.isBlank(fields)) return null;
        JSONArray arr = new JSONArray();

        //所有字段
        if(StringUtils.equals(fields, "*"))
        {
            JSONArray o = this.m_table.read("$.fields");

            return JSONArray.fromObject(o);
        }

        String[] fs = fields.split(",");
        for(String f : fs)
        {
            String q = String.format("$.fields[?(@.name=='%s')]",f);
            net.minidev.json.JSONArray o = this.m_table.read(q);

            arr.add( o.get(0) );
        }
        return arr;
    }

    public JSONObject selPK()
    {
        JSONArray fs = this.m_table.read("fields[?(@.identity==true && @.primary==true)]");
        return JSONObject.fromObject( fs.get(0) );
    }

    /**
     * 从变量中选择字段信息
     * @param ps
     * @return
     */
    public JSONArray selFields(SqlParam[] ps)
    {
        JSONArray arr = new JSONArray();

        for(SqlParam p : ps)
        {
            String q = String.format("$.fields[?(@.name=='%s')]",p.m_name);
            XDebug.Output(q);
            net.minidev.json.JSONArray o = this.m_table.read(q);

            arr.add( o.get(0) );
        }
        return arr;
    }

    public String toFields(SqlParam[] sp)
    {
        if(null == sp) return "";
        ArrayList<String> arr = new ArrayList<String>();
        for(SqlParam p : sp)
        {
            arr.add(p.m_name);
        }
        String[] arrs = arr.toArray(new String[arr.size()]);
        return StringUtils.join(arrs,",");
    }

    /**
     * 转换成SQL条件语句
     * @param sp
     * @return name=? and age=?
     */
    public String toSqlConditions(SqlParam[] sp) {
        if(sp.length == 0) return "1=1";

        ArrayList<String> arr = new ArrayList<String>();
        for(SqlParam p : sp)
        {
            String v = String.format("%s=?", p.m_name);
            arr.add(v);
        }
        String[] vs = arr.toArray(new String[arr.size()]);
        return StringUtils.join(vs, " and ");
    }

    /**
     * 转换成值设置语句 name=?,age=?,type=?
     * @param fields
     * @return
     */
    public String toSqlSet(String fields) {
        String[] field_arr = StringUtils.split(fields, ",");
        ArrayList<String> arr = new ArrayList<String>();
        for(String f : field_arr)
        {
            arr.add( String.format("%s=?", f) );
        }
        String[] s = arr.toArray(new String[arr.size()]);
        return StringUtils.join(s, ",");
    }

    public String toSqlSet(SqlParam[] fields) {
        ArrayList<String> arr = new ArrayList<String>();
        for(SqlParam p : fields)
        {
            String v = String.format("%s=?", p.m_name);
            arr.add(v);
        }
        String[] vs = arr.toArray(new String[arr.size()]);
        return StringUtils.join(vs, ",");
    }

    /**
     * 转换成条件变量语句 id=? and age=?
     * @param names
     * @return
     */
    public String toSqlWhere(String names)
    {
        String[] field_arr = StringUtils.split(names, ",");


        ArrayList<String> arr = new ArrayList<String>();
        for(String f : field_arr)
        {
            arr.add( String.format("%s=?", f) );
        }
        String[] s = arr.toArray(new String[arr.size()]);
        return StringUtils.join(s, " and ");
    }
    public String toSqlWhere(SqlParam[] ps)
    {
        ArrayList<String> arr = new ArrayList<String>();
        for(SqlParam f : ps)
        {
            arr.add( String.format("%s=?", f.m_name) );
        }
        String[] s = arr.toArray(new String[arr.size()]);
        return StringUtils.join(s, " and ");
    }

    /**
     *
     * @param ps
     * @param pre 条件谓词 and,or
     * @return
     */
    public String toSqlWhere(SqlParam[] ps,String pre)
    {
        ArrayList<String> arr = new ArrayList<String>();
        for(SqlParam f : ps)
        {
            arr.add( String.format("%s=?", f.m_name) );
        }
        String[] s = arr.toArray(new String[arr.size()]);
        return StringUtils.join(s, String.format(" %s ",pre));
    }

    /**
     * 转换成条件变量语句 ?,?,?
     * @param names
     * @return
     */
    public String toParam(String names)
    {
        String[] field_arr = StringUtils.split(names, ",");


        ArrayList<String> arr = new ArrayList<String>();
        for(String f : field_arr)
        {
            arr.add( "?" );
        }
        String[] s = arr.toArray(new String[arr.size()]);
        return StringUtils.join(s, ",");
    }
    public String toParam(SqlParam[] ps)
    {
        ArrayList<String> arr = new ArrayList<String>();
        for(int i = 0 ; i < ps.length ; ++i)
        {
            arr.add( "?" );
        }
        String[] s = arr.toArray(new String[arr.size()]);
        return StringUtils.join(s, ",");
    }

    public JSONObject read(String table,String fields,SqlParam[] ps)
    {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);
        JSONArray field_condition = this.selFields(ps);
        String sql = String.format("select %s from %s where %s", fields,table,this.toSqlConditions(ps));

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.m_svs.set(cmd, field_condition, ps);
        ResultSet r = db.ExecuteDataSet(cmd);
        JSONObject o = new JSONObject();
        try {
            if(r.next())
            {
                SqlCmdReader scr = new SqlCmdReader();
                o = scr.read(r, field_sel);
            }
            r.close();
            cmd.getConnection().close();
            cmd.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return o;
    }

    public void insert(String table,String fields,JSONObject o)
    {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);

        String sql = String.format("insert into %s ( %s ) values( %s );"
                , table
                , fields
                , this.toParam(fields));

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.m_svs.set(cmd, field_sel, o);
        db.ExecuteNonQuery( cmd );
    }

    public void insert(String table, SqlParam[] pars) {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(pars);


        String sql = String.format("insert into %s ( %s ) values( %s );"
                , table
                , this.toFields(pars)
                , this.toParam(pars));

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.m_svs.set(cmd, field_sel, pars);
        db.ExecuteNonQuery(cmd);
    }

    public void update(String table,String fields,String where,JSONObject obj)
    {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);
        JSONArray field_were = this.selFields(where);
        String sql = String.format("update %s set %s where %s", table,this.toSqlSet(fields),this.toSqlWhere(where));

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.m_svs.set(cmd, field_sel,obj);
        this.m_svs.set(cmd, field_were,obj);
        db.ExecuteNonQuery(cmd);
    }

    public void update(String table,SqlParam[] fields,String where)
    {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);

        String sql = String.format("update %s set %s where %s", table,this.toSqlSet(fields),where);

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.m_svs.set(cmd, field_sel, fields);
        db.ExecuteNonQuery(cmd);
    }

    public void update(String table,SqlParam[] fields,SqlParam[] where)
    {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);
        JSONArray field_where = this.selFields(where);

        String sql = String.format("update %s set %s where %s",
                table,
                this.toSqlSet(fields),
                this.toSqlWhere(where));

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.m_svs.set(cmd, field_sel, fields);
        this.m_svs.set(cmd, field_where, where,fields.length);
        db.ExecuteNonQuery(cmd);
    }

    /**
     *
     * @param table
     * @param fields
     * @param where
     * @param predicate 条件谓词：and,or
     */
    public void update(String table,SqlParam[] fields,SqlParam[] where,String predicate)
    {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);
        JSONArray field_where = this.selFields(where);

        String sql = String.format("update %s set %s where %s",
                table,
                this.toSqlSet(fields),
                this.toSqlWhere(where,predicate));

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.m_svs.set(cmd, field_sel, fields);
        this.m_svs.set(cmd, field_where, where,fields.length);
        db.ExecuteNonQuery(cmd);
    }

    public void delete(String table,SqlParam[] where)
    {
        this.m_table = this.table(table);
        JSONArray field_where = this.selFields(where);

        String sql = String.format("delete from %s where %s", table,this.toSqlWhere(where));

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.m_svs.set(cmd, field_where, where);
        db.ExecuteNonQuery(cmd);
    }

    public int count(String table,SqlParam[] where)
    {
        this.m_table = this.table(table);
        String ws = this.toSqlConditions(where);
        JSONArray field_where = this.selFields(where);
        String sql = String.format("select count(*) from %s where %s", table,ws);

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.m_svs.set(cmd,field_where,where);
        Object v = db.ExecuteScalar(cmd);
        return Integer.parseInt(v.toString());
    }

    public int count(String table,String where)
    {
        this.m_table = this.table(table);

        String sql = String.format("select count(*) from %s where %s", table,where);

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        Object v = db.ExecuteScalar(cmd);
        return Integer.parseInt(v.toString());
    }

    public JSONArray select(String table,String fields,SqlParam[] where,String sort)
    {
        SqlCmdReader scr = new SqlCmdReader();
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);
        JSONArray field_where = this.selFields(where);

        String sql = String.format("select %s from %s where %s", fields,table,this.toSqlConditions(where));
        if( null == where )
        {
            sql = String.format("select %s from %s", fields,table);
        }

        if(!StringUtils.isBlank( sort) )
        {
            sql = String.format("select %s from %s where %s order by %s", fields,table,this.toSqlConditions(where),sort);
        }

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        this.m_svs.set(cmd, field_where, where);//设置条件参数
        ResultSet r = db.ExecuteDataSet(cmd);
        JSONArray arr = new JSONArray();

        try {
            while(r.next())
            {
                JSONObject o = scr.read(r, field_sel);
                arr.add(o);
            }
            r.close();
            cmd.getConnection().close();
            cmd.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return arr;
    }

    public JSONArray select(String table,String fields,String where,String sort)
    {
        SqlCmdReader scr = new SqlCmdReader();
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);

        if( !StringUtils.isBlank(where) ) where = String.format("where %s", where);
        if( !StringUtils.isBlank(sort)) sort = String.format(" order by %s", sort);

        String sql = String.format("select %s from %s %s %s", fields,table,where,sort);

        DbHelper db = new DbHelper();
        PreparedStatement cmd = db.GetCommand(sql);
        ResultSet r = db.ExecuteDataSet(cmd);
        JSONArray arr = new JSONArray();

        try {
            while(r.next())
            {
                JSONObject o = scr.read(r, field_sel);
                arr.add(o);
            }
            r.close();
            cmd.getConnection().close();
            cmd.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return arr;
    }

    public JSONArray page(String table,String primaryKey,String fields,int pageSize,int pageIndex,String where,String sort)
    {
        DBConfig cfg = new DBConfig();
        if(StringUtils.equals(cfg.m_db, "oracle")) return this.page_oracle(table, primaryKey, fields, pageSize, pageIndex, where, sort);
        if(StringUtils.equals(cfg.m_db, "mysql")) return this.page_mysql(table, primaryKey, fields, pageSize, pageIndex, where, sort);

        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);

        JSONArray arr = new JSONArray();
        try {

            DbHelper db = new DbHelper();
            CallableStatement cmd = db.GetCommandStored("{call spPager(?,?,?,?,?,?,?,?)}");
            cmd.setString(1, table);
            cmd.setString(2, primaryKey);
            cmd.setInt(3, pageSize);
            cmd.setInt(4, pageIndex);
            cmd.setBoolean(5, false);
            cmd.setString(6, where);
            cmd.setString(7, sort);
            cmd.setString(8, fields);
            ResultSet r = db.ExecuteDataSet(cmd);
            while(r.next())
            {
                JSONObject item = new JSONObject();
                for(int i = 0 ; i < field_sel.size();++i)
                {
                    JSONObject column = (JSONObject) field_sel.get(i);
                    Object v = this.m_scr.read(r, column.getString("type"), i+2);//第一列是行号
                    item.put(column.getString("name"),v);
                }
                arr.add(item);
            }
            r.close();
            cmd.getConnection().close();
            cmd.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return arr;
    }

    public JSONArray page_mysql(String table,String primaryKey,String fields,int pageSize,int pageIndex,String where,String sort)
    {
        int rowStart = pageSize *(pageIndex-1);
        if( !StringUtils.isBlank(where)) where = "where " + where;
        if( !StringUtils.isBlank(sort)) sort = "order by " + sort;
        String sql = String.format("select %s from %s %s %s limit %d,%d"
                ,fields,table,where,sort,rowStart,pageSize);
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);

        JSONArray arr = new JSONArray();
        try {

            DbHelper db = new DbHelper();
            PreparedStatement cmd = db.GetCommand(sql);
            ResultSet r = db.ExecuteDataSet(cmd);
            while(r.next())
            {
                JSONObject item = new JSONObject();
                for(int i = 0 ; i < field_sel.size();++i)
                {
                    JSONObject column = (JSONObject) field_sel.get(i);
                    Object v = this.m_scr.read(r, column.getString("type"), i+1);
                    item.put(column.getString("name"),v);
                }
                arr.add(item);
            }
            r.close();
            cmd.getConnection().close();
            cmd.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return arr;
    }

    public JSONArray page_oracle(String table,String primaryKey,String fields,int pageSize,int pageIndex,String where,String sort)
    {
        this.m_table = this.table(table);
        JSONArray field_sel = this.selFields(fields);

        JSONArray arr = new JSONArray();
        try {
            int pageStart = (pageIndex - 1)*(pageSize - 1);
            int pageEnd = (pageIndex - 1) * pageSize + pageSize;
            String sql = String.format("select * from (select rownum rn ,a.* from(select %s from %s where %s) a where rownum <= %d ) where rn >= %d",fields,table,where,pageEnd,pageStart);

            DbHelper db = new DbHelper();
            PreparedStatement cmd = db.GetCommand(sql);
            ResultSet r = db.ExecuteDataSet(cmd);
            while(r.next())
            {
                JSONObject item = new JSONObject();
                for(int i = 0 ; i < field_sel.size();++i)
                {
                    JSONObject column = field_sel.getJSONObject(i);
                    Object v = this.m_scr.read(r, column.getString("type"), i+2);//第一列是行号
                    item.put(column.getString("name"),v);
                }
                arr.add(item);
            }
            r.close();
            cmd.getConnection().close();
            cmd.close();

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return arr;
    }
}